﻿Imports System.Linq
Imports System.Xml
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.ObjectModel
Imports System.Data.Metadata.Edm

Module Converter
    Dim DestinationFolderPath As String = ""
    Dim bCreateNavigation As Boolean
    Dim SourceFolderPath As String = ""
    Dim namespaceName As String = ""
    Public Const quote As String = """"
    Private Const StoreGeneratedMetadata As String = "http://schemas.microsoft.com/ado/2009/02/edm/annotation:StoreGeneratedPattern"

    Dim myxmlstring As String


    Public Sub ConvertFile(ByVal srcpath As String, ByVal dstpath As String, ByVal CreateNavigation As Boolean)
        Try
            SourceFolderPath = srcpath '"C:\data\EdmxToWakandaDBConverterTool\Edmx To WakandaDB ConverterTool\Test Edmx  Csdl-Msl-Ssdl Files\Simple" '
            DestinationFolderPath = dstpath '"C:\data\EdmxToWakandaDBConverterTool\Edmx To WakandaDB ConverterTool\Test Output Folder" '
            bCreateNavigation = CreateNavigation
            ' Construct a new instance of the metadata workspace.
            Dim workspace As MetadataWorkspace = New MetadataWorkspace()

            ' Create a new item collection with the specified 
            ' connection and the folder info 
            ' where the entity data model (EDM) schemas exist.
            Dim edmCollection As New EdmItemCollection(SourceFolderPath)

            ' Register the item collection with the metadata workspace.
            workspace.RegisterItemCollection(edmCollection)

            '' Get the entity containers in the conceptual model.
            ConvertEDMX(workspace, DataSpace.CSpace)


        Catch exceptionMetadata As MetadataException
            Console.WriteLine("MetadataException: {0}", _
                exceptionMetadata.Message)
        Catch exceptionMapping As MappingException
            Console.WriteLine("MappingException: {0}", _
               exceptionMapping.Message)
        End Try



    End Sub
    Public Sub ConvertEDMX( _
ByVal workspace As MetadataWorkspace, ByVal model As DataSpace)
        Dim containers As ReadOnlyCollection(Of EntityContainer) = _
           workspace.GetItems(Of EntityContainer)(model)

        Dim PropertyName As String = ""
        Dim DataType As String = ""
        Dim MaxLength As Integer
        Dim Percision As String = ""
        Dim Scale As String = ""
        Dim DefaultValue As String = ""
        Dim Unicode As Boolean = False
        Dim FixedLength As Boolean = False
        Dim IsPrimaryKey As Boolean = False
        Dim IsNullable As Boolean = False
        Dim IsAutoSequence As Boolean = False
        Dim IsNavigationAttribute As Boolean = False
        Dim IsUnique As Boolean = False
        Dim AttributeCount As Integer = 1
        Dim TableCount As Integer = 1
        Dim IsForeignKeyComponent As Boolean
        Dim IsGenerated As Boolean
        Dim IsGeneratedType As MetadataProperty

        namespaceName = workspace.GetItems(Of EntityType)(DataSpace.CSpace).First().NamespaceName
        BuildDocHead(namespaceName)
        TableCount = 1
        AttributeCount = 1
        Dim tableentities = workspace.GetItems(Of EntityType)(DataSpace.CSpace)
        For Each tableentity In tableentities

            BuildTableEntity(tableentity.Name, TableCount)

            Dim spk As New List(Of String)
            'Primary Keysmember
            For Each pk In tableentity.KeyMembers
                spk.Add(pk.Name)
            Next

            'Coumns
            For Each member In tableentity.Members.OfType(Of EdmProperty)()

                If member IsNot Nothing Then
                    IsForeignKeyComponent = DetermineIsForeignKeyComponent(member, IsPrimaryKey)
                    IsGenerated = IsServerGenerated(member, IsGeneratedType)
                End If

                'If column is foreignkey then skip , it is handled in navigation properties
                If Not IsForeignKeyComponent Then
                    If IsGenerated = True Then
                        IsUnique = True
                        IsAutoSequence = True
                    Else
                        IsUnique = False
                        IsAutoSequence = False
                    End If
                    'Primary Key
                    If spk.Contains(member.Name) Then
                        IsPrimaryKey = True
                    Else
                        IsPrimaryKey = False
                    End If

                    ' Check for Nullable property
                    If member.Nullable = True Then
                        IsNullable = True
                    Else
                        IsNullable = False
                    End If

                    'Check Data Type
                    If member.TypeUsage.EdmType.Name.Length > 0 Then
                        DataType = member.TypeUsage.EdmType.Name
                    End If

                    'Check Name
                    If member.Name.Length > 0 Then
                        PropertyName = member.Name
                    End If

                    'Check Facets ie MaxLength,Percision,Scale,Unicode,FixedLength,DefaultValue
                    For Each facet As Facet In member.TypeUsage.Facets
                        If Not facet.IsUnbounded Then
                            If Not facet.Value Is Nothing Then
                                If facet.Name = "MaxLength" Then
                                    MaxLength = facet.Value
                                ElseIf facet.Name = "Percision" Then
                                    Percision = facet.Value
                                ElseIf facet.Name = "Scale" Then
                                    Scale = facet.Value
                                ElseIf facet.Name = "Unicode" Then
                                    Unicode = facet.Value
                                ElseIf facet.Name = "FixedLength" Then
                                    FixedLength = facet.Value
                                ElseIf facet.Name = "DefaultValue" Then
                                    DefaultValue = facet.Value
                                End If
                            End If
                        End If
                    Next
                    ' IsAutoSequence = False
                    DataType = MapTypes.StorageDataType(DataType)
                    If Not IsNavigationAttribute Then
                        BuildTableEntityAttributes(PropertyName, DataType, MaxLength, Percision, _
                                                   Scale, DefaultValue, Unicode, FixedLength, _
                                                   IsPrimaryKey, IsNullable, IsAutoSequence, IsUnique, AttributeCount)

                        AttributeCount = AttributeCount + 1
                    End If
                    IsGeneratedType = Nothing
                End If

            Next member

            If bCreateNavigation Then
                'GetNavigatio Properties of Entity
                For Each navProp In tableentity.Members.OfType(Of NavigationProperty)()
                    Dim AttributeName As String = ""
                    Dim IsReversePath As Boolean
                    Dim NavPath As String
                    Dim NavType As String
                    Dim Kind As String
                    Dim ToMultiplicity As String

                    ToMultiplicity = navProp.ToEndMember.RelationshipMultiplicity.ToString
                    If ToMultiplicity = "Many" Then
                        AttributeName = navProp.Name & "Collection"
                        IsReversePath = True
                        NavType = navProp.ToEndMember.GetEntityType.Name & "Collection"
                        NavPath = navProp.FromEndMember.GetEntityType.Name.ToLower
                        Kind = "relatedEntities"
                    ElseIf ToMultiplicity = "ZeroOrOne" Then
                        AttributeName = navProp.Name.ToLower
                        IsReversePath = False
                        NavType = navProp.ToEndMember.GetEntityType.Name
                        NavPath = navProp.ToEndMember.GetEntityType.Name
                        Kind = "relatedEntity"
                    ElseIf ToMultiplicity = "" Then
                    Else
                        'Error
                    End If

                    BuildTableEntityNavigationProperties(AttributeName, IsReversePath, NavPath, NavType, Kind, AttributeCount)
                    AttributeCount = AttributeCount + 1
                Next navProp
            End If

            BuidTableEntityEnd()
            TableCount = TableCount + 1
            AttributeCount = 1

        Next tableentity

        BuildDocEnd()
        CreateXmlDoc()
    End Sub

    Private Sub BuildDocHead(ByVal namespaceName As String)

        myxmlstring = "<?xml version=" & quote & "1.0" & quote & " encoding=" & quote & "UTF-8" & quote & "?><EntityModelCatalog>" &
            "<dbInfo name=" & quote & namespaceName & quote & "  uuid=" & quote & GenerateGuid() & quote & "/>"



    End Sub
    Private Sub BuildTableEntity(ByVal Name As String, ByVal Position As Integer)
        myxmlstring = myxmlstring & "<dataClasses name=" & quote & Name & quote & " className=" & quote & Name & quote & " collectionName=" & quote & Name & "Collection" & quote & " scope=" & quote & "public" & quote & " uuid=" & quote & GenerateGuid() & quote & " tablePos=" & quote & Position.ToString & quote & ">" &
        "<extraProperties ____objectunic=" & quote & "true" & quote & " panelColor=" & quote & "#76923C" & quote & ">" &
        "<panel ____objectunic=" & quote & "true" & quote & " isOpen=" & quote & "true" & quote & " pathVisible=" & quote & "true" & quote & ">" &
        "<position ____objectunic=" & quote & "true" & quote & " X=" & quote & "366" & quote & " Y=" & quote & "36" & quote & "/>" &
        "</panel></extraProperties>"
    End Sub
    Private Sub BuidTableEntityEnd()
        myxmlstring = myxmlstring & "</dataClasses>"
    End Sub
    Private Sub BuildTableEntityAttributes(PropertyName, DataType, MaxLength, Percision, Scale, DefaultValue, _
                                           Unicode, FixedLength, IsPrimaryKey, IsNullable, IsAutoSequence, IsUnique, FieldPosition)
        Dim PlaceHolder As String = Nothing
        If MaxLength > 0 Then
            PlaceHolder = PlaceHolder & " MaxLength=" & quote & MaxLength & quote
        ElseIf Percision.Length > 0 Then
            PlaceHolder = PlaceHolder & " Percision=" & quote & Percision & quote
        ElseIf FixedLength = True Then
            PlaceHolder = PlaceHolder & " FixedLength=" & quote & FixedLength & quote
        ElseIf Unicode = True Then
            PlaceHolder = PlaceHolder & " Unicode=" & quote & Unicode & quote
        ElseIf Scale.Length > 0 Then
            PlaceHolder = PlaceHolder & " Scale=" & quote & Scale & quote
        ElseIf DefaultValue.Length > 0 Then
            PlaceHolder = PlaceHolder & " DefaultValue=" & quote & DefaultValue & quote
        End If

        If Not PlaceHolder Is Nothing Then
            myxmlstring = myxmlstring & "<attributes name=" & quote & PropertyName & quote & " kind=" & quote & "storage" & quote & " scope=" & quote & "public" & quote & " uuid=" & quote & GenerateGuid() & quote & " fieldPos=" & quote & FieldPosition.ToString & quote & " unique=" & quote & IsUnique & quote & " autosequence=" & quote & IsAutoSequence & quote & " type=" & quote & DataType & quote & " primKey=" & quote & IsPrimaryKey & quote & PlaceHolder & "/>"
        Else
            myxmlstring = myxmlstring & "<attributes name=" & quote & PropertyName & quote & " kind=" & quote & "storage" & quote & " scope=" & quote & "public" & quote & " uuid=" & quote & GenerateGuid() & quote & " fieldPos=" & quote & FieldPosition.ToString & quote & " unique=" & quote & IsUnique & quote & " autosequence=" & quote & IsAutoSequence & quote & " type=" & quote & DataType & quote & " primKey=" & quote & IsPrimaryKey & quote & "/>"
        End If


    End Sub
    Private Sub BuildTableEntityNavigationProperties(AttributeName, IsReversePath, NavPath, NavType, Kind, FieldPosition)
        If IsReversePath = True Then
            myxmlstring = myxmlstring & "<attributes name=" & quote & AttributeName & quote & " kind=" & quote & Kind & quote & " scope=" & quote & "public" & quote & " type=" & quote & NavType & quote & " reversePath=" & quote & IsReversePath & quote & " path=" & quote & NavPath & quote & "/>"
        Else
            myxmlstring = myxmlstring & "<attributes name=" & quote & AttributeName & quote & " kind=" & quote & Kind & quote & " scope=" & quote & "public" & quote & " uuid=" & quote & GenerateGuid() & quote & " fieldPos=" & quote & FieldPosition.ToString & quote & " type=" & quote & NavType & quote & " reversePath=" & quote & IsReversePath & quote & " path=" & quote & NavPath & quote & "/>"

        End If

    End Sub

    Private Sub BuildDocEnd()
        myxmlstring = myxmlstring & "</EntityModelCatalog>"
    End Sub


    Public Sub CreateXmlDoc()

        Dim myfile As String = DestinationFolderPath & "\" & namespaceName & ".waModel"
        ''Set string to write:
        'Dim objWriter As New System.IO.StreamWriter(myfile)
        ''Write string:
        'objWriter.Write(myxmlstring)
        ''Close objectwriter:
        'objWriter.Close()

        Dim xmlElem = XElement.Parse(myxmlstring)
        ' Console.WriteLine(xmlElem)
        xmlElem.Save(myfile)
        FormMain.lblStatus.Text = "Conversion Completed. File saved to " & myfile

    End Sub
    Public Function GenerateGuid() As String
        Return Guid.NewGuid.ToString
    End Function
    Private Function DetermineIsForeignKeyComponent(ByVal [property] As EdmProperty, ByVal IsPrimaryKey As Boolean) As Boolean
        Dim navigationProperties = [property].DeclaringType.Members.OfType(Of NavigationProperty)()

        ' Look at all NavigationProperties (i.e. strongly-type relationship columns) of the table this column belong to and 
        ' see if there is a foreign key that matches this property
        ' If this is a 1 to 0..1 relationship and we are processing the more primary side. i.e in the Student in Student-StudentDetail
        ' and this is the primary key we don't want to check the relationship type since if there are no constraints we will treat the primary key as a foreign key.
        Return navigationProperties.Any(Function(n) GetDependentPropertyNames(n, (Not IsPrimaryKey)).Contains([property].Name)) ' checkRelationshipType
    End Function

    Friend Function GetDependentPropertyNames(ByVal navigationProperty As NavigationProperty) As IEnumerable(Of String)
        Return GetDependentPropertyNames(navigationProperty, True) 'checkRelationshipType
    End Function
    Private Function IsServerGenerated(ByVal [property] As EdmProperty, ByRef IsGeneratedType As MetadataProperty) As Boolean

        If [property].MetadataProperties.TryGetValue(StoreGeneratedMetadata, False, IsGeneratedType) Then
            Return "Identity" = CStr(IsGeneratedType.Value) OrElse "Computed" = CStr(IsGeneratedType.Value)
        End If
        Return False
    End Function
    Friend Function GetDependentPropertyNames(ByVal navigationProperty As NavigationProperty, ByVal checkRelationshipType As Boolean) As IEnumerable(Of String)
        If checkRelationshipType Then
            If navigationProperty.ToEndMember.RelationshipMultiplicity = RelationshipMultiplicity.ZeroOrOne AndAlso navigationProperty.FromEndMember.RelationshipMultiplicity = RelationshipMultiplicity.One Then
                ' Get constraint when this association is on the "parent" (aka "from") side. This means
                ' that the navProperty represents a Children association in a 1-1 relationship. For example,
                ' this could be a Item-ItemDetail scenario where the ItemDetail table has a PK that is also an FK
                ' into the Item table (thus ensuring the 1-1 cardinality). We need to special case this situation because normally we would try
                ' to build a foreign key name of the form "Item.ItemID", but we want just "ItemID".
                Dim relationshipType As AssociationType = CType(navigationProperty.RelationshipType, AssociationType)
                Dim constraint As ReferentialConstraint = relationshipType.ReferentialConstraints.FirstOrDefault(Function(c) c.ToRole Is navigationProperty.ToEndMember)
                If constraint IsNot Nothing Then
                    Return constraint.FromProperties.Select(Function(p) p.Name)
                End If

                ' Fall back on the primary keys if no constraints were found but only if we are on the parent side. i.e the 1 side Item side in an Item-ItemDetail
                ' Get the primary keys on the "from" side of the relationship. i.e Product.Category -> ProductID
                Return navigationProperty.FromEndMember.GetEntityType().KeyMembers.Select(Function(m) m.Name)
            End If
        End If
        Return navigationProperty.GetDependentProperties().Select(Function(m) m.Name)
    End Function


End Module